https://www.kaggle.com/new-york-state/nys-children-in-foster-care-annually https://www.ncsc.org/Microsites/EveryKid/Home/Data-and-Reform-Efforts/Data-By-State.aspx https://www.acf.hhs.gov/cb/resource/trends-in-foster-care-and-adoption
library(readxl)
library(sf)
library(tidyverse)
library(viridis)
library(rvest)
library(plotly)#national dataset
nation_data<-read_excel("data/national_afcars_trends_2009_through_2018.xlsx",sheet="Data")
#State dataset
#Numbers of Children Served in Foster Care, by State
state_served <- read_excel("data/afcars_state_data_tables_09thru18.xlsx",range="Served!A8:K60") %>%
gather(year,Served,'FY 2009':'FY 2018')
#Numbers of Children in Foster Care on September 30th, by State
state_inCare <- read_excel("data/afcars_state_data_tables_09thru18.xlsx",range="In Care on September 30th!A8:K60") %>%
gather(year,InCare_Sep30,'FY 2009':'FY 2018')
#Numbers of Children Entering Foster Care, by State
state_entered <- read_excel("data/afcars_state_data_tables_09thru18.xlsx",range="Entered!A8:K60") %>%
gather(year,Entered,'FY 2009':'FY 2018')
#Numbers of Children Exiting Foster Care, by State
state_exited <- read_excel("data/afcars_state_data_tables_09thru18.xlsx",range="Exited!A8:K60") %>%
gather(year,Exited,'FY 2009':'FY 2018')
#Numbers of Children Waiting for Adoption, by State
state_waitingAdoption <- read_excel("data/afcars_state_data_tables_09thru18.xlsx",range="Waiting for Adoption!A8:K60") %>%
gather(year,Waiting_Adoption,'FY 2009':'FY 2018')
#Numbers of Children Waiting for Adoption Whose Parental Rights Have Been Terminated, by State
state_parentalRightsTerminated <- read_excel("data/afcars_state_data_tables_09thru18.xlsx",range="Parental Rights Terminated!A8:K60") %>%
gather(year,parental_rights_terminated,'FY 2009':'FY 2018')
#Numbers of Children Adopted, by State
state_adopted <- read_excel("data/afcars_state_data_tables_09thru18.xlsx",range="Adopted!A8:K60") %>%
gather(year,adopted,'FY 2009':'FY 2018')merge_cols<-c("State","year")
#The merge argument only takes two values as input, so you have to do them separately:
#state_df<- merge(state_served,state_inCare,state_entered,state_exited,state_waitingAdoption,state_parentalRightsTerminated,state_adopted,by=c("State","year"))
state_data<- merge(state_served,state_inCare,by=merge_cols)
state_data<- merge(state_data,state_entered,by=merge_cols)
state_data<- merge(state_data,state_exited,by=merge_cols)
state_data<- merge(state_data,state_waitingAdoption,by=merge_cols)
state_data<- merge(state_data,state_parentalRightsTerminated,by=merge_cols)
state_data<- merge(state_data,state_adopted,by=merge_cols)head(state_data)## State year Served InCare_Sep30 Entered Exited Waiting_Adoption
## 1 Alabama FY 2009 9677 6179 3080 3498 1475
## 2 Alabama FY 2010 8119 5350 3063 2770 1271
## 3 Alabama FY 2011 8395 5253 3257 3143 1297
## 4 Alabama FY 2012 7907 4561 2763 3346 1156
## 5 Alabama FY 2013 7322 4435 3041 2888 1084
## 6 Alabama FY 2014 7520 4526 3192 2994 1044
## parental_rights_terminated adopted
## 1 882 638
## 2 757 606
## 3 701 447
## 4 543 587
## 5 615 532
## 6 573 544us_states <- st_read("./shp/states.shp")## Reading layer `states' from data source `/Volumes/GoogleDrive/My Drive/RWorkspace/VisualAnalytics-FinalProject/shp/states.shp' using driver `ESRI Shapefile'
## Simple feature collection with 51 features and 5 fields
## geometry type: MULTIPOLYGON
## dimension: XY
## bbox: xmin: -178.2176 ymin: 18.92179 xmax: -66.96927 ymax: 71.40624
## epsg (SRID): 4269
## proj4string: +proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defsas_tibble(us_states)## # A tibble: 51 x 6
## STATE_NAME DRAWSEQ STATE_FIPS SUB_REGION STATE_ABBR geometry
## <fct> <int> <fct> <fct> <fct> <MULTIPOLYGON [°]>
## 1 Hawaii 1 15 Pacific HI (((-160.0738 22.00418, -…
## 2 Washington 2 53 Pacific WA (((-122.402 48.22522, -1…
## 3 Montana 3 30 Mountain MT (((-111.4754 44.70216, -…
## 4 Maine 4 23 New Engla… ME (((-69.77728 44.07415, -…
## 5 North Dak… 5 38 West Nort… ND (((-98.73044 45.93827, -…
## 6 South Dak… 6 46 West Nort… SD (((-102.7884 42.9953, -1…
## 7 Wyoming 7 56 Mountain WY (((-104.0536 41.69822, -…
## 8 Wisconsin 8 55 East Nort… WI (((-87.74856 44.96162, -…
## 9 Idaho 9 16 Mountain ID (((-117.0263 43.67903, -…
## 10 Vermont 10 50 New Engla… VT (((-73.25806 42.74606, -…
## # … with 41 more rowsstate_data_2009 <- state_data %>% filter(year == 'FY 2009') %>% rename(STATE_NAME = State)us_states_mapped <- inner_join(us_states,state_data_2009,by="STATE_NAME")## Warning: Column `STATE_NAME` joining factor and character vector, coercing into
## character vectorg <- us_states_mapped %>%
ggplot() +
geom_sf(aes(fill = Served,text = paste0("State: ",STATE_NAME,", Year : ", year))) +
# coord_sf(crs = st_crs(102003)) +
scale_fill_viridis("Served",begin = 0.06,end=0.95,option = "plasma") +
ggtitle("Orphans Served by each state in 2009") +
theme_bw()
ggplotly(g,tooltip = c("text","fill"))g <- us_states_mapped %>%
ggplot() +
geom_sf(aes(fill = Entered,text = paste0("State: ",STATE_NAME,", Year : ", year))) +
scale_fill_distiller("Entered",palette = "YlOrBr") +
ggtitle("Orphans Entered by each state in 2009") +
theme_minimal() +
theme(legend.position = "bottom",
axis.text.x=element_blank(),
axis.text.y=element_blank(),
axis.ticks.x=element_blank(),
axis.ticks.y=element_blank(),
panel.grid.major = element_blank())
ggplotly(g,tooltip = c("text","fill"))g <- us_states_mapped %>%
ggplot() +
geom_sf(aes(fill = Exited,text = paste0("State: ",STATE_NAME,", Year : ", year))) +
scale_fill_distiller("Exited",palette = "RdYlGn") +
ggtitle("Orphans Exited by each state in 2009") +
theme_minimal() +
theme(legend.position = "bottom",
axis.text.x=element_blank(),
axis.text.y=element_blank(),
axis.ticks.x=element_blank(),
axis.ticks.y=element_blank(),
panel.grid.major = element_blank())
ggplotly(g,tooltip = c("text","fill"))